package com.zxy.supplier_system.server.repository

import com.zxy.supplier_system.server.entity.BusinessGeneralGoodsCategory
import com.zxy.supplier_system.server.entity.GeneralGoodsCategory
import com.zxy.supplier_system.server.entity.SystemService
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface BusinessGeneralGoodsCategoryRepository: JpaRepository<BusinessGeneralGoodsCategory, BusinessGeneralGoodsCategory.UnionId> {

    fun findAllBySystemService(systemService: SystemService):List<BusinessGeneralGoodsCategory>


    @Query("""
        WITH RECURSIVE category_path AS (
          SELECT g.id, g.name, g.parent_id
          FROM general_goods_category g
          INNER JOIN business_general_goods_category b ON g.id = b.category_id
          WHERE b.system_service_id = ?1
          
          UNION ALL
          
          -- 递归查询：选择父节点
          SELECT c.id, c.name, c.parent_id
          FROM general_goods_category c
          INNER JOIN category_path cp ON c.id = cp.parent_id
        )
SELECT * FROM category_path
    """, nativeQuery = true)
    fun findFlatCategoriesBySystemServiceId(systemServiceId: String): List<GeneralGoodsCategory>

}
